﻿CREATE PROCEDURE [dbo].[ETL_SP_SearchVDtSourcePagedList]
	@DtSerId uniqueidentifier,
	@HasTask char,
	@start int,
	@limit int
AS
if @HasTask = 'Y' or @HasTask = 'y'
begin 
	Select T.*,
		(select DT.Name 
			from ETL_DtTable as DT
			inner join ETL_DtSourceMapping as DSM ON DSM.DtSerId = @DtSerId 
					and DSM.DtSourceId = T.Id 
					and DT.Id = DSM.DtTableId) as MappingTable
	from(
		SELECT ROW_NUMBER() OVER(Order BY DS.Name) as _RowIndex,
			DS.*,
			DSM1.DtTableId as MappingTableId
		from ETL_DtSource as DS
			LEFT JOIN ETL_DtSourceMapping as DSM1 on DSM1.DtSourceId = DS.Id
		WHERE DS.DtSerId = @DtSerId
					and DS.Id in (select TASK.DtSourceId from ETL_Task as TASK)
		) as T
	where
		T._RowIndex > @start and T._RowIndex <= @start+@limit
end
else if @HasTask = 'N' or @HasTask = 'n'
begin
	Select T.*,
		(select DT.Name 
			from ETL_DtTable as DT
			inner join ETL_DtSourceMapping as DSM ON DSM.DtSerId = @DtSerId 
					and DSM.DtSourceId = T.Id 
					and DT.Id = DSM.DtTableId) as MappingTable
	from(
		SELECT ROW_NUMBER() OVER(Order BY DS.Name) as _RowIndex,
			DS.*,
			DSM1.DtTableId as MappingTableId
		from ETL_DtSource as DS
			LEFT JOIN ETL_DtSourceMapping as DSM1 on DSM1.DtSourceId = DS.Id
		WHERE DS.DtSerId = @DtSerId
					and DS.Id not in (select TASK.DtSourceId from ETL_Task as TASK)) as T
	where
		T._RowIndex > @start and T._RowIndex <= @start+@limit
end
else
begin
	Select T.*,
		(select DT.Name 
			from ETL_DtTable as DT
			inner join ETL_DtSourceMapping as DSM ON DSM.DtSerId = @DtSerId 
					and DSM.DtSourceId = T.Id 
					and DT.Id = DSM.DtTableId) as MappingTable
	from(
		SELECT ROW_NUMBER() OVER(Order BY DS.Name) as _RowIndex,
			DS.*,
			DSM1.DtTableId as MappingTableId
		from ETL_DtSource as DS
			LEFT JOIN ETL_DtSourceMapping as DSM1 on DSM1.DtSourceId = DS.Id
		WHERE DS.DtSerId = @DtSerId) as T
	where
		T._RowIndex > @start and T._RowIndex <= @start+@limit
end
RETURN 0
